﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;

namespace Super.Common.DB
{
   public class AccessHelper
    {


       

       /// <summary>
        /// 读取mdb内容（完全读取）
       /// </summary>
       /// <param name="tableName"></param>
       /// <param name="mdbPath"></param>
       /// <param name="success"></param>
        /// <returns>返回的是一个DataTable，如需其他格式可以自行转换。</returns>
       public static DataTable ReadAllData(string tableName, string mdbPath, ref bool success)
       {
           DataTable dt = new DataTable();
           try
           {
               DataRow dr;
               //1、建立连接   
               string strConn
                   = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPath ;
               OleDbConnection odcConnection = new OleDbConnection(strConn);

               //string strConn
               //  = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\Project\\JCSoft\\Super\\dic.ldb;Jet OLEDB:Database";
               //OleDbConnection odcConnection = new OleDbConnection(strConn);

               //2、打开连接   
               odcConnection.Open();
               //建立SQL查询   
               OleDbCommand odCommand = odcConnection.CreateCommand();
               //3、输入查询语句   
               odCommand.CommandText = "select * from " + tableName;
               //建立读取   
               OleDbDataReader odrReader = odCommand.ExecuteReader();
               //查询并显示数据   
               int size = odrReader.FieldCount;
               for (int i = 0; i < size; i++)
               {
                   DataColumn dc;
                   dc = new DataColumn(odrReader.GetName(i));
                   dt.Columns.Add(dc);
               }
               while (odrReader.Read())
               {
                   dr = dt.NewRow();
                   for (int i = 0; i < size; i++)
                   {
                       dr[odrReader.GetName(i)] = odrReader[odrReader.GetName(i)].ToString();
                   }
                   dt.Rows.Add(dr);
               }
               //关闭连接   
               odrReader.Close();
               odcConnection.Close();
               success = true;
               return dt;
           }
           catch
           {
               success = false;
               return dt;
           }
       }


       // 读取mdb数据  
       /// <summary>
       /// 读取mdb内容（按列读取）
       /// </summary>
       /// <param name="mdbPaht"></param>
       /// <param name="tableName"></param>
       /// <param name="columns"></param>
       /// <param name="success"></param>
       /// <returns>columns数组存储的是你要查询的列名称（必须确保mdb表中存在你要的列）</returns>
       public static DataTable ReadDataByColumns(string mdbPaht, string tableName, string[] columns, ref bool success)
       {
           DataTable dt = new DataTable();
           try
           {
               DataRow dr;
               //1、建立连接   
               string strConn
                   = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbPaht + ";Jet OLEDB:Database Password=haoren";
               OleDbConnection odcConnection = new OleDbConnection(strConn);
               //2、打开连接   
               odcConnection.Open();
               //建立SQL查询   
               OleDbCommand odCommand = odcConnection.CreateCommand();
               //3、输入查询语句   
               string strColumn = "";
               for (int i = 0; i < columns.Length; i++)
               {
                   strColumn += columns[i].ToString() + ",";
               }
               strColumn = strColumn.TrimEnd(',');
               odCommand.CommandText = "select " + strColumn + " from " + tableName;
               //建立读取   
               OleDbDataReader odrReader = odCommand.ExecuteReader();
               //查询并显示数据   
               int size = odrReader.FieldCount;
               for (int i = 0; i < size; i++)
               {
                   DataColumn dc;
                   dc = new DataColumn(odrReader.GetName(i));
                   dt.Columns.Add(dc);
               }

               while (odrReader.Read())
               {
                   dr = dt.NewRow();
                   for (int i = 0; i < size; i++)
                   {
                       dr[odrReader.GetName(i)] = odrReader[odrReader.GetName(i)].ToString();
                   }
                   dt.Rows.Add(dr);
               }
               //关闭连接   
               odrReader.Close();
               odcConnection.Close();
               success = true;
               return dt;
           }
           catch
           {
               success = false;
               return dt;
           }
       }  
    }
}
